/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.ipti.br.tag.dao;

import org.ipti.br.tag.entity.*;
import org.ipti.br.tag.entity.enums.PersonageType;
import org.ipti.br.tag.entity.enums.Reference;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.RollbackException;
import javax.persistence.criteria.*;

/**
 *
 * @author israel
 */
@Stateless
public class ActorFacade extends AbstractFacade<Actor> {

    @PersistenceContext(unitName = "TAG2PU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public ActorFacade() {
        super(Actor.class);
    }

    @Override
    public void create(Actor actor) {
        getEntityManager().persist(actor);
    }

    @Override
    public void edit(Actor actor) {
        getEntityManager().merge(actor);
    }

    public Actor findByFields(Person person, Personage personage, Unity unity) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Actor> from = cq.from(Actor.class);
        cq.where(
                cb.and(
                cb.equal(from.join(Actor_.person).get(Person_.id), person.getId()),
                cb.equal(from.join(Actor_.personage).get(Personage_.id), personage.getId()),
                cb.equal(from.join(Actor_.unity).get(Unity_.id), unity.getId()),
                cb.lessThanOrEqualTo(from.get(Actor_.activated), Calendar.getInstance())),
                cb.or(
                cb.greaterThanOrEqualTo(from.get(Actor_.deactivated), Calendar.getInstance()),
                cb.isNull(from.get(Actor_.deactivated))));

        try {
            return getEntityManager().createQuery(cq).getSingleResult();
        } catch (Exception ex) {
            return null;
        }
    }

    public List<Actor> actorActivatedPerPerson(Long personID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Actor> from = cq.from(Actor.class);
        Predicate predicate1 = cb.equal(from.get(Actor_.person).get(Person_.id), personID);
        Predicate predicate2 = cb.lessThanOrEqualTo(from.get(Actor_.activated), Calendar.getInstance());
        Predicate predicate3 = cb.greaterThanOrEqualTo(from.get(Actor_.deactivated), Calendar.getInstance());
        Predicate predicate4 = cb.isNull(from.get(Actor_.deactivated));
        cq.where(cb.and(predicate1, predicate2), cb.or(predicate3, predicate4));

        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> actorsPerPerson(Long personID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Actor> from = cq.from(Actor.class);
        Predicate predicate = cb.equal(from.get(Actor_.person).get(Person_.id), personID);
        cq.where(cb.and(predicate));

        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> actorActivatedPerUnity(Long unityID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Actor> from = cq.from(Actor.class);
        Predicate predicate1 = cb.equal(from.get(Actor_.unity).get(Unity_.id), unityID);
        Predicate predicate2 = cb.lessThanOrEqualTo(from.get(Actor_.activated), Calendar.getInstance());
        Predicate predicate3 = cb.greaterThanOrEqualTo(from.get(Actor_.deactivated), Calendar.getInstance());
        Predicate predicate4 = cb.isNull(from.get(Actor_.deactivated));
        cq.where(cb.and(predicate1, predicate2), cb.or(predicate3, predicate4));

        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> actorsPerUnity(Long unityID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Actor> from = cq.from(Actor.class);
        Predicate predicate = cb.equal(from.get(Actor_.unity).get(Unity_.id), unityID);
        cq.where(cb.and(predicate));

        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> searchActorActivatedPerPersonPersonage(Long personID, Long personageID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Actor> root = cq.from(Actor.class);
        Predicate predicate1 = cb.equal(root.get(Actor_.person).get(Person_.id), personID);
        Predicate predicate2 = cb.lessThanOrEqualTo(root.get(Actor_.activated), Calendar.getInstance());
        Predicate predicate3 = cb.greaterThanOrEqualTo(root.get(Actor_.deactivated), Calendar.getInstance());
        Predicate predicate4 = cb.isNull(root.get(Actor_.deactivated));
        Predicate predicate5 = cb.equal(root.get(Actor_.personage).get(Personage_.id), personageID);
        cq.where(cb.and(predicate1, predicate2, predicate5), cb.or(predicate3, predicate4));

        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> searchActorActivatedPerUnityPersonage(Long unityID, Long personageID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Actor> root = cq.from(Actor.class);
        Predicate predicate1 = cb.equal(root.get(Actor_.unity).get(Unity_.id), unityID);
        Predicate predicate2 = cb.lessThanOrEqualTo(root.get(Actor_.activated), Calendar.getInstance());
        Predicate predicate3 = cb.greaterThanOrEqualTo(root.get(Actor_.deactivated), Calendar.getInstance());
        Predicate predicate4 = cb.isNull(root.get(Actor_.deactivated));
        Predicate predicate5 = cb.equal(root.get(Actor_.personage).get(Personage_.id), personageID);
        cq.where(cb.and(predicate1, predicate2, predicate5), cb.or(predicate3, predicate4));

        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> searchActorPerUnityPersonage(Long unityID, Long personageID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Actor> root = cq.from(Actor.class);
        Predicate predicate1 = cb.equal(root.get(Actor_.unity).get(Unity_.id), unityID);
        Predicate predicate2 = cb.equal(root.get(Actor_.personage).get(Personage_.id), personageID);
        cq.where(cb.and(predicate1, predicate2));

        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> searchActorActivatedPerPersonage(Long personageID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Actor> root = cq.from(Actor.class);
        Predicate predicate2 = cb.lessThanOrEqualTo(root.get(Actor_.activated), Calendar.getInstance());
        Predicate predicate3 = cb.greaterThanOrEqualTo(root.get(Actor_.deactivated), Calendar.getInstance());
        Predicate predicate4 = cb.isNull(root.get(Actor_.deactivated));
        Predicate predicate5 = cb.equal(root.get(Actor_.personage).get(Personage_.id), personageID);
        cq.where(cb.and(predicate2, predicate5), cb.or(predicate3, predicate4));

        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> modifiedActorBetweenDate(Calendar initialDate, Calendar finalDate, Long serialNumber) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Actor> from = cq.from(Actor.class);
        Predicate predicate1 = cb.notEqual(from.get(Actor_.serialNumber), serialNumber);
        Predicate predicate2 = cb.greaterThanOrEqualTo(from.get(Actor_.lastModified), initialDate);
        Predicate predicate3 = cb.lessThan(from.get(Actor_.lastModified), finalDate);
        cq.where(cb.and(predicate1, predicate2, predicate3));

        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> actorsPerUnityPersonage(Long unityID, Long personageID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Actor> from = cq.from(Actor.class);
        Predicate predicate1 = cb.equal(from.get(Actor_.unity).get(Unity_.id), unityID);
        Predicate predicate2 = cb.equal(from.get(Actor_.personage).get(Personage_.id), personageID);
        Predicate predicate3 = cb.equal(from.get(Actor_.personage).get(Personage_.personageFather).get(Personage_.id), personageID);
        cq.where(cb.and(predicate1, cb.or(predicate2, predicate3)));

        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> searchActorActivatedPerPersonageUnityName(String humanName, Long unityID, Long personageID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        cq.select(actors);
        cq.where(cb.and(
                cb.like(cb.upper(cb.concat(root.get(Human_.firstName), root.get(Human_.lastName))), "%" + humanName.toUpperCase() + "%"),
                cb.equal(actors.join(Actor_.unity).get(Unity_.id), unityID),
                cb.equal(actors.join(Actor_.personage).get(Personage_.id), personageID),
                cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance())),
                cb.or(
                cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                cb.isNull(actors.get(Actor_.deactivated))));


        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> searchActorActivatedPerPersonageUnitiesName(String humanName, List<Long> unities, Long personageID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        cq.select(actors);


        List<Predicate> predicateList = new ArrayList<Predicate>();
        for (Long l : unities) {
            predicateList.add(cb.equal(actors.join(Actor_.unity).get(Unity_.id), l));
        }

        if (predicateList.isEmpty()) {
            return new ArrayList<Actor>();
        } else if (predicateList.size() == 1) {
            cq.where(
                    cb.and(
                    cb.like(cb.upper(cb.concat(root.get(Human_.firstName), root.get(Human_.lastName))), "%" + humanName.toUpperCase() + "%"),
                    cb.equal(actors.join(Actor_.personage).get(Personage_.id), personageID),
                    cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance())),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    predicateList.get(0));
        } else {
            cq.where(cb.and(
                    cb.like(cb.upper(cb.concat(root.get(Human_.firstName), root.get(Human_.lastName))), "%" + humanName.toUpperCase() + "%"),
                    cb.equal(actors.join(Actor_.personage).get(Personage_.id), personageID),
                    cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance())),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    cb.or(predicateList.toArray(new Predicate[0])));
        }

        cq.orderBy(cb.asc(root.get(Human_.firstName)),
                cb.asc(root.get(Human_.lastName)));

        cq.orderBy(cb.asc(root.get(Human_.firstName)),
                cb.asc(root.get(Human_.lastName)));
        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> searchActorActivatedPerPersonageUnities(List<Long> unities, Long personageID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        cq.select(actors);

        List<Predicate> predicateList = new ArrayList<Predicate>();
        for (Long l : unities) {
            predicateList.add(cb.equal(actors.join(Actor_.unity).get(Unity_.id), l));
        }

        if (predicateList.isEmpty()) {
            return new ArrayList<Actor>();
        } else if (predicateList.size() == 1) {
            cq.where(
                    cb.and(
                    cb.equal(actors.join(Actor_.personage).get(Personage_.id), personageID),
                    cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance())),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    predicateList.get(0));
        } else {
            cq.where(cb.and(
                    cb.equal(actors.join(Actor_.personage).get(Personage_.id), personageID),
                    cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance())),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    cb.or(predicateList.toArray(new Predicate[0])));
        }

        cq.orderBy(cb.asc(root.get(Human_.firstName)),
                cb.asc(root.get(Human_.lastName)));
        return getEntityManager().createQuery(cq).getResultList();
    }

    public List<Actor> searchActorActivatedPerPersonageUnities(List<Long> unities, Long personageID,
            int first, int pageSize, Map<String, String> filters) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        cq.select(actors);

        List<Predicate> unitiesPredicateList = new ArrayList<Predicate>();
        for (Long l : unities) {
            unitiesPredicateList.add(cb.equal(actors.join(Actor_.unity).get(Unity_.id), l));
        }

        List<Predicate> predicateList = new ArrayList<Predicate>();
        if (personageID != null) {
            predicateList.add(cb.equal(actors.join(Actor_.personage).get(Personage_.id), personageID));
        }
        predicateList.add(cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance()));

        for (String filter : filters.keySet()) {
            Predicate p = null;
            if (filter.equals("")) {
                p = cb.like(cb.upper(cb.concat(root.get(Human_.firstName), root.get(Human_.lastName))), "%" + filters.get(filter).toUpperCase() + "%");
            } else if (filter.equals("unity.name")) {
                p = cb.like(cb.upper(actors.join(Actor_.unity).get(Unity_.name)), "%" + filters.get(filter).toUpperCase() + "%");
            }

            if (p != null) {
                predicateList.add(p);
            }
        }

        String filter = filters.get("name");
        if(filter != null && !filter.isEmpty()) {
            Predicate p = cb.like(cb.upper(cb.concat(root.get(Human_.firstName), root.get(Human_.lastName))), "%" + filter.toUpperCase() + "%");
            predicateList.add(p);
        }
        
        if (unitiesPredicateList.isEmpty()) {
            return new ArrayList<Actor>();
        } else if (unitiesPredicateList.size() == 1) {
            cq.where(
                    cb.and(predicateList.toArray(new Predicate[0])),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    unitiesPredicateList.get(0));
        } else {
            cq.where(cb.and(predicateList.toArray(new Predicate[0])),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    cb.or(unitiesPredicateList.toArray(new Predicate[0])));
        }

        cq.orderBy(cb.asc(root.get(Human_.firstName)),
                cb.asc(root.get(Human_.lastName)));

        Query q = getEntityManager().createQuery(cq);
        q.setFirstResult(first);
        q.setMaxResults(pageSize);
        return q.getResultList();
    }

    public int getActorCount(List<Long> unities, Long personageID) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery cq = cb.createQuery();
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        cq.select(cb.count(actors));


        List<Predicate> unitiesPredicateList = new ArrayList<Predicate>();
        for (Long l : unities) {
            unitiesPredicateList.add(cb.equal(actors.join(Actor_.unity).get(Unity_.id), l));
        }

        if (unitiesPredicateList.isEmpty()) {
            return 0;
        } else if (unitiesPredicateList.size() == 1) {
            if (personageID != null) {
                cq.where(
                        cb.and(
                        cb.equal(actors.join(Actor_.personage).get(Personage_.id), personageID),
                        cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance())),
                        cb.or(
                        cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                        cb.isNull(actors.get(Actor_.deactivated))),
                        unitiesPredicateList.get(0));
            } else {
                cq.where(
                        cb.and(
                        cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance())),
                        cb.or(
                        cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                        cb.isNull(actors.get(Actor_.deactivated))),
                        unitiesPredicateList.get(0));
            }
        } else {
            if (personageID != null) {
                cq.where(cb.and(
                        cb.equal(actors.join(Actor_.personage).get(Personage_.id), personageID),
                        cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance())),
                        cb.or(
                        cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                        cb.isNull(actors.get(Actor_.deactivated))),
                        cb.or(unitiesPredicateList.toArray(new Predicate[0])));
            } else {
                cq.where(cb.and(
                        cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance())),
                        cb.or(
                        cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                        cb.isNull(actors.get(Actor_.deactivated))),
                        cb.or(unitiesPredicateList.toArray(new Predicate[0])));
            }
        }
        return ((Long) getEntityManager().createQuery(cq).getSingleResult()).intValue();
    }

//consultas na tela de unitySchedule    
    public List<Actor> searchActorActivatedPerUnitySchedule(List<Long> unitySchedules,
            int first, int pageSize, Map<String, String> filters) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        Join<Actor, ActorSchedule> actorSchedules = actors.join(Actor_.actorSchedules);
        cq.select(actors);


        List<Predicate> predicateList = new ArrayList<Predicate>();
        for (Long l : unitySchedules) {
            predicateList.add(cb.equal(actorSchedules.get(ActorSchedule_.referenceID), l));
        }

        List<Predicate> andPredicates = new ArrayList<Predicate>();
        andPredicates.add(cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance()));
        andPredicates.add(cb.equal(actorSchedules.get(ActorSchedule_.reference), Reference.UNITY));
        for (String filter : filters.keySet()) {
            Predicate p = null;
            if (filter.equals("")) {
                p = cb.like(cb.upper(cb.concat(root.get(Human_.firstName), root.get(Human_.lastName))), "%" + filters.get(filter).toUpperCase() + "%");
            }

            if (p != null) {
                andPredicates.add(p);
            }
        }

        if (predicateList.isEmpty()) {
            return new ArrayList<Actor>();
        } else if (predicateList.size() == 1) {
            cq.where(
                    cb.and(andPredicates.toArray(new Predicate[0])),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    predicateList.get(0));
        } else {
            cq.where(cb.and(andPredicates.toArray(new Predicate[0])),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    cb.or(predicateList.toArray(new Predicate[0])));
        }

        cq.orderBy(cb.asc(root.get(Human_.firstName)),
                cb.asc(root.get(Human_.lastName)));
        Query q = getEntityManager().createQuery(cq);
        q.setFirstResult(first);
        q.setMaxResults(pageSize);
        return q.getResultList();
    }
    //consultas na tela de unitySchedule    

    public List<Actor> searchActivatedActor(List<UnitySchedule> unitySchedules) {
        if (unitySchedules.isEmpty()) {
            return new ArrayList<Actor>();
        }

        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        Join<Actor, ActorSchedule> actorSchedules = actors.join(Actor_.actorSchedules);
        cq.select(actors);

        List<Predicate> predicateList = new ArrayList<Predicate>();
        for (UnitySchedule us : unitySchedules) {
            predicateList.add(cb.equal(actorSchedules.get(ActorSchedule_.referenceID), us.getId()));
        }

        Predicate p1 = cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance());
        Predicate p2 = cb.equal(actorSchedules.get(ActorSchedule_.reference), Reference.UNITY);

        cq.where(
                cb.and(p1, p2),
                cb.or(
                cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                cb.isNull(actors.get(Actor_.deactivated))),
                cb.or(predicateList.toArray(new Predicate[0])));

        cq.orderBy(cb.asc(root.get(Human_.firstName)),
                cb.asc(root.get(Human_.lastName)));
        Query q = getEntityManager().createQuery(cq);
        return q.getResultList();
    }

    public List<Actor> searchActivatedActor(Unity unity) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        cq.select(actors);

        Predicate p = cb.equal(actors.get(Actor_.unity).get(Unity_.id), unity.getId());
        Predicate p1 = cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance());
        Predicate p2 = cb.equal(actors.join(Actor_.personage).get(Personage_.personageType), PersonageType.ALLOCATED);

        cq.where(
                cb.and(p, p1, p2),
                cb.or(
                cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                cb.isNull(actors.get(Actor_.deactivated))));

        cq.orderBy(cb.asc(root.get(Human_.firstName)),
                cb.asc(root.get(Human_.lastName)));
        Query q = getEntityManager().createQuery(cq);
        return q.getResultList();
    }

    public int getActorCountPerUnitySchedule(List<Long> unitySchedules) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery cq = cb.createQuery(Actor.class);
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        Join<Actor, ActorSchedule> actorSchedules = actors.join(Actor_.actorSchedules);
        cq.select(cb.count(actors));


        List<Predicate> predicateList = new ArrayList<Predicate>();
        for (Long l : unitySchedules) {
            predicateList.add(cb.equal(actorSchedules.get(ActorSchedule_.referenceID), l));
        }

        if (predicateList.isEmpty()) {
            return 0;
        } else if (predicateList.size() == 1) {
            cq.where(
                    cb.and(
                    cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance()),
                    cb.equal(actorSchedules.get(ActorSchedule_.reference), Reference.UNITY)),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    predicateList.get(0));
        } else {
            cq.where(cb.and(
                    cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance()),
                    cb.equal(actorSchedules.get(ActorSchedule_.reference), Reference.UNITY)),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    cb.or(predicateList.toArray(new Predicate[0])));
        }

        return ((Long) getEntityManager().createQuery(cq).getSingleResult()).intValue();
    }
//fim

    public List<Actor> searchActorActivatedPerUnitiesAllocated(List<Long> unities,
            int first, int pageSize, Map<String, String> filters) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        cq.select(actors);

        List<Predicate> unitiesPredicateList = new ArrayList<Predicate>();
        for (Long l : unities) {
            unitiesPredicateList.add(cb.equal(actors.join(Actor_.unity).get(Unity_.id), l));
        }

        List<Predicate> predicateList = new ArrayList<Predicate>();
        predicateList.add(cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance()));
        predicateList.add(cb.equal(actors.join(Actor_.personage).get(Personage_.personageType), PersonageType.ALLOCATED));

        for (String filter : filters.keySet()) {
            Predicate p = null;
            if (filter.equals("")) {
                p = cb.like(cb.upper(cb.concat(root.get(Human_.firstName), root.get(Human_.lastName))), "%" + filters.get(filter).toUpperCase() + "%");
            } else if (filter.equals("unity.name")) {
                p = cb.like(cb.upper(actors.join(Actor_.unity).get(Unity_.name)), "%" + filters.get(filter).toUpperCase() + "%");
            }

            if (p != null) {
                predicateList.add(p);
            }
        }

        if (unitiesPredicateList.isEmpty()) {
            return new ArrayList<Actor>();
        } else if (unitiesPredicateList.size() == 1) {
            cq.where(
                    cb.and(predicateList.toArray(new Predicate[0])),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    unitiesPredicateList.get(0));
        } else {
            cq.where(cb.and(predicateList.toArray(new Predicate[0])),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    cb.or(unitiesPredicateList.toArray(new Predicate[0])));
        }

        cq.orderBy(cb.asc(root.get(Human_.firstName)),
                cb.asc(root.get(Human_.lastName)));

        Query q = getEntityManager().createQuery(cq);
        q.setFirstResult(first);
        q.setMaxResults(pageSize);
        return q.getResultList();
    }

    public int getActorCountAllocated(List<Long> unities) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery cq = cb.createQuery();
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        cq.select(cb.count(actors));


        List<Predicate> unitiesPredicateList = new ArrayList<Predicate>();
        for (Long l : unities) {
            unitiesPredicateList.add(cb.equal(actors.join(Actor_.unity).get(Unity_.id), l));
        }

        if (unitiesPredicateList.isEmpty()) {
            return 0;
        } else if (unitiesPredicateList.size() == 1) {
            cq.where(
                    cb.and(
                    cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance()),
                    cb.equal(actors.join(Actor_.personage).get(Personage_.personageType), PersonageType.ALLOCATED)),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    unitiesPredicateList.get(0));
        } else {
            cq.where(cb.and(
                    cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance()),
                    cb.equal(actors.join(Actor_.personage).get(Personage_.personageType), PersonageType.ALLOCATED)),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    cb.or(unitiesPredicateList.toArray(new Predicate[0])));
        }
        return ((Long) getEntityManager().createQuery(cq).getSingleResult()).intValue();
    }

    public List<Actor> searchActorActivatedPerUnitiesMatriculated(List<Long> unities) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        cq.select(actors);

        List<Predicate> unitiesPredicateList = new ArrayList<Predicate>();
        for (Long l : unities) {
            unitiesPredicateList.add(cb.equal(actors.join(Actor_.unity).get(Unity_.id), l));
        }

        List<Predicate> predicateList = new ArrayList<Predicate>();
        predicateList.add(cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance()));
        predicateList.add(cb.equal(actors.join(Actor_.personage).get(Personage_.personageType), PersonageType.MATRICULATED));

        if (unitiesPredicateList.isEmpty()) {
            return new ArrayList<Actor>();
        } else if (unitiesPredicateList.size() == 1) {
            cq.where(
                    cb.and(predicateList.toArray(new Predicate[0])),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    unitiesPredicateList.get(0));
        } else {
            cq.where(cb.and(predicateList.toArray(new Predicate[0])),
                    cb.or(
                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                    cb.isNull(actors.get(Actor_.deactivated))),
                    cb.or(unitiesPredicateList.toArray(new Predicate[0])));
        }

        cq.orderBy(cb.asc(root.get(Human_.firstName)),
                cb.asc(root.get(Human_.lastName)));

        Query q = getEntityManager().createQuery(cq);
        return q.getResultList();
    }

    public List<Actor> list(Unity unity, Discipline discipline, PersonageType pt) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        cq.select(actors);

        Predicate p = cb.equal(actors.join(Actor_.unity).get(Unity_.id), unity.getId());
        Predicate p1 = cb.equal(actors.join(Actor_.personage).get(Personage_.personageType), pt);
        if (pt == PersonageType.ALLOCATED) {
            Predicate p2;
            if (discipline != null) {
                p2 = cb.equal(actors.join(Actor_.actorSchedules)
                        .get(ActorSchedule_.discipline).get(Discipline_.id), discipline.getId());
            } else {
                p2 = cb.isNotEmpty(actors.get(Actor_.actorSchedules));
            }
            cq.where(p, p1, p2);
        } else {
            cq.where(p, p1);
        }

        cq.distinct(true);
        cq.orderBy(cb.asc(root.get(Human_.firstName)),
                cb.asc(root.get(Human_.lastName)));

        Query q = getEntityManager().createQuery(cq);
        return q.getResultList();
    }

    public List<Actor> listAllocateds(UnitySchedule unitySchedule) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Actor> cq = cb.createQuery(Actor.class);
        Root<Human> root = cq.from(Human.class);
        Join<Human, Actor> actors = root.join(Human_.actors);
        Join<Actor, ActorSchedule> actorSchedules = actors.join(Actor_.actorSchedules);
        cq.select(actors);

        Predicate p = cb.equal(actorSchedules.get(ActorSchedule_.referenceID), unitySchedule.getId());
        Predicate p1 = cb.equal(actorSchedules.get(ActorSchedule_.reference), Reference.UNITY_SCHEDULE);
        Predicate p2 = cb.equal(actors.join(Actor_.personage).get(Personage_.personageType), PersonageType.ALLOCATED);
//        Predicate p3 = cb.lessThanOrEqualTo(actors.get(Actor_.activated), Calendar.getInstance());

        cq.where(p, p1, p2 //                ,cb.or(
                //                    cb.greaterThanOrEqualTo(actors.get(Actor_.deactivated), Calendar.getInstance()),
                //                    cb.isNull(actors.get(Actor_.deactivated))
                //                )
                );

        cq.orderBy(cb.asc(root.get(Human_.firstName)),
                cb.asc(root.get(Human_.lastName)));
        Query q = getEntityManager().createQuery(cq);
        return q.getResultList();
    }

    public void removePerformances(Actor actor) {
        
        try {
            em.createNativeQuery("DELETE from performance where actor_id = "
                    + actor.getId() + " and grade = 0").executeUpdate();

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}
